# _*_ coding:utf-8 _*_
# @Time  : 2022.12.16
# @Author: zizlee
import re
import time
import sys
# sys.path.append('E:/Projects/RuizyServer')
from RuizyServer.db import FAConnection, RuizyEDBConnection


def extra_formula_index(formula_explain):  # 提取公式的指标列
    table_name_str = re.sub(r'[()]', '', formula_explain)
    table_name_str = re.sub(r'[*/+-]', ',', table_name_str)
    parse_index = table_name_str.split(',')
    return list(filter(lambda x: x.startswith('wind_') or x.startswith('ths_') or x.startswith('rj_'), parse_index))


def set_personal_sheet():
    with FAConnection() as c:
        c.execute("""
            SELECT a.*,b.user_id,b.variety_en FROM datalib_sheet_column As a
            INNER JOIN datalib_variety_sheet As b ON a.sheet_id=b.id;
        """)
        records = c.fetchall()

    # 解析出指标
    user_index = []
    for item in records:
        formula_indexes = extra_formula_index(item['formula'])
        for s in formula_indexes:
            _o = {
                    'user_id': item['user_id'],
                    'formula': s,
                    'variety_en': item['variety_en']
                }
            if _o not in user_index:
                user_index.append(_o)

    with RuizyEDBConnection() as r:
        # 查找指标表的ID
        for index in user_index:
            sql = 'SELECT id FROM all_ruizy_sheet WHERE source_plat=%s AND source_id=%s LIMIT 1;'
            r.execute(sql, index['formula'].split('_'))
            ret = r.fetchone()
            if not ret:
                raise ValueError(f'{index}没有查询到ID')
            index['ruizy_sheet_id'] = ret['id']
            time.sleep(0.001)

    # 插入数据
    with RuizyEDBConnection() as ir:
        c = ir.executemany("""
            INSERT INTO all_personal_sheet (user_id,variety_en,ruizy_sheet_id)
            VALUES (%(user_id)s,%(variety_en)s,%(ruizy_sheet_id)s);
        """, user_index)
        print('插入完成:', c)


if __name__ == '__main__':
    set_personal_sheet()
